package jeopardy.server.db;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.nio.CharBuffer;
import java.nio.charset.Charset;
import java.security.MessageDigest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.LinkedList;
import java.util.List;

import jeopardy.common.protocol.game.play.GameRecordMsg;
import jeopardy.common.protocol.game.stats.GameRec;
import jeopardy.common.protocol.game.stats.UserScore;
import jeopardy.server.ServerConsts;

import org.apache.log4j.Logger;
import org.h2.jdbcx.JdbcDataSource;

import com.google.gson.Gson;

public class H2ServerDb {
	private static Logger log = Logger.getLogger(H2ServerDb.class);

	private boolean ready = false;
	private JdbcDataSource ds;
	private Connection conn;
	
	private PreparedStatement createUserPstmt = null;
	private PreparedStatement lookupUserPstmt = null;
	private PreparedStatement authUserPstmt = null;
	
	private PreparedStatement getPlayingGamesPstmt = null;
	private PreparedStatement getWaitingGamesPstmt = null;
	
	private PreparedStatement createGamePstmt = null;
	private PreparedStatement startGamePstmt = null;
	private PreparedStatement finishGamePstmt = null;

	private MessageDigest md = null;

	private Gson gson = new Gson();

	private PreparedStatement rankersPstmt;

	private PreparedStatement gamesPstmt;
	
	public H2ServerDb(String url, String user, char[] pass){
		try{
			md = MessageDigest.getInstance(ServerConsts.USERACCOUNT_MD_ALG);
		}catch(Exception e){
			log.error("Failed to initialize md5 digest!");
			log.debug("Failed to initialize md5 digest!", e);
		}
		ds = new JdbcDataSource();
		ds.setURL(url);
		ds.setUser(user);
		ds.setPasswordChars(pass);
		init();
	}
	
	private synchronized void init(){

		if(!ready){
			close();
			
			log.info("Initializing DB Connection...");
			
			try{
				conn = ds.getConnection();
				lookupUserPstmt = conn.prepareStatement("SELECT count(*) as numusers from USERS where NAME = ?");
				createUserPstmt = conn.prepareStatement("INSERT into USERS (NAME, PASS) values (?, ?)");
				authUserPstmt = conn.prepareStatement("SELECT count(*) as numusers from USERS where NAME = ? and PASS = ?");
				getPlayingGamesPstmt = conn.prepareStatement("SELECT * from current_games where waiting = 0");
				getWaitingGamesPstmt = conn.prepareStatement("SELECT * from current_games where waiting = 1");
				
				createGamePstmt = conn.prepareStatement("INSERT into CURRENT_GAMES (ID, WAITING, START_TIME) values (?, 1, ?)");
				startGamePstmt = conn.prepareStatement("UPDATE CURRENT_GAMES set WAITING = 0, START_TIME = ?");
				finishGamePstmt = conn.prepareStatement("INSERT into GAME_RECORDS(CREATOR, WINNER, POINTS_WON, START_TIME, GAMELOG) values (?, ?, ?, ?, ?)");
				rankersPstmt = conn.prepareStatement("select winner, sum(points_won) as points from game_records group by winner order by points desc limit 20;");
				gamesPstmt = conn.prepareStatement("SELECT * from game_records order by start_time desc");
				
			}catch(Exception e){
				log.error("error initializing connection");
				log.debug("error initializing connection", e);
			}
			ready = true;
		}
		
	}
	private synchronized void close(){
		try{
			if(conn != null) conn.close();
		}catch(Exception e){
			log.error("error trying to close db connection");
			log.debug("error trying to close db connection", e);
		}
		try{
			if(createUserPstmt != null) createUserPstmt.close();
		}catch(Exception e){
			log.error("error trying to close preparedstatement");
			log.debug("error trying to close preparedstatement", e);
		}
		try{
			if(lookupUserPstmt != null) lookupUserPstmt.close();
		}catch(Exception e){
			log.error("error trying to close preparedstatement");
			log.debug("error trying to close preparedstatement", e);
		}
	}

	private boolean userExists(String user) throws Exception{
		try{
			init();
			lookupUserPstmt.clearParameters();
			lookupUserPstmt.setString(1, user);
			ResultSet rs = lookupUserPstmt.executeQuery();
			
			rs.next();
			boolean res = rs.getInt("numusers") == 1;
			rs.close();
			lookupUserPstmt.clearParameters();
			return res;
		}catch(Exception e){
			ready = false;
			throw new Exception("Error checking for user");
		}
	}
	
	public boolean authUser(String user, char[] pass){
		try{
			init();
			authUserPstmt.clearParameters();
			authUserPstmt.setString(1, user);
			authUserPstmt.setBytes(2, getPasswordHash(pass));
			ResultSet rs = authUserPstmt.executeQuery();
			
			rs.next();
			boolean res = rs.getInt("numusers") == 1;
			rs.close();
			authUserPstmt.clearParameters();
			return res;
		}catch(Exception e){
			ready = false;
			log.info("Error authenticating user");
			log.debug("Error authenticating user", e);
			return false;
		}
	}

	public synchronized boolean createUser(String user, char[] pass) throws Exception{
		if(userExists(user)){
			return false;
		}
		try{
			init();
			createUserPstmt.clearParameters();
			createUserPstmt.setString(1, user);
			createUserPstmt.setBytes(2, getPasswordHash(pass));
			boolean success = createUserPstmt.executeUpdate() == 1;
			createUserPstmt.clearParameters();
			return success;
		}catch(Exception e){
			ready = false;
			throw new Exception("Error creating user");
		}
	}
	
	private byte[] getPasswordHash(char[] pass) throws Exception{
		
		byte[] bytes = Charset.forName("UTF-8").encode(CharBuffer.wrap(pass)).array();
		md.update(ServerConsts.USERACCOUNT_PASS_SALT);
		md.update(bytes);
		byte[] digest = md.digest();
		return digest;
	}

	public void gameStoreFinishedGame(GameRecordMsg gr) {
		try{
			init();
			InputStream gameHistory = new ByteArrayInputStream(gson.toJson(gr.getHistory()).getBytes());
			finishGamePstmt.clearParameters();
			
			finishGamePstmt.setString(1, gr.getCreator());
			finishGamePstmt.setString(2, gr.getWinner());
			finishGamePstmt.setInt(3, gr.getPointsWon());
			finishGamePstmt.setTimestamp(4, new Timestamp(gr.getTimeStarted()));
			finishGamePstmt.setBlob(5, gameHistory);
			
			finishGamePstmt.execute();
			finishGamePstmt.clearBatch();
			gameHistory.close();
		}catch(Exception e){
			ready = false;
		}
	}

	public List<String> getPlayingGames() {
		try{
			init();
			ResultSet rs = getPlayingGamesPstmt.executeQuery();
			List<String> gameIds = new LinkedList<String>();
			while(rs.next()){
				String gameId = rs.getString("ID");
				gameIds.add(gameId);
			}
			
			return gameIds;
		}catch(Exception e){
			ready = false;
			return null;
		}
	}

	public List<String> getWaitingGames() {
		try{
			init();
			ResultSet rs = getWaitingGamesPstmt.executeQuery();
			List<String> gameIds = new LinkedList<String>();
			while(rs.next()){
				String gameId = rs.getString("ID");
				gameIds.add(gameId);
			}
			
			return gameIds;
		}catch(Exception e){
			ready = false;
			return null;
		}
	}

	public void createGame(String id, String creator) {
		try{
			init();
			createGamePstmt.clearParameters();
			createGamePstmt.setString(1, id);
			createGamePstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			createGamePstmt.execute();
			
			createGamePstmt.clearParameters();
		}catch(Exception e){
			ready = false;
		}
	}

	public void startGame(String gid, List<String> initialClients) {
		try{
			init();
			startGamePstmt.clearParameters();
			startGamePstmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
			startGamePstmt.execute();
			
			startGamePstmt.clearParameters();
		}catch(Exception e){
			ready = false;
		}
	}

	public UserScore[] getRankers() {
		List<UserScore> rankers = new LinkedList<UserScore>();	
		try{
			init();
			ResultSet rs = rankersPstmt.executeQuery();
			
			while(rs.next()){
				String ranker = rs.getString("winner");
				int points = rs.getInt("points");
				UserScore us = new UserScore(ranker, points);
				rankers.add(us);
			}
			rs.close();
		}catch(Exception e){
			ready = false;
		}
		return rankers.toArray(new UserScore[0]);
	}

	public GameRec[] getGames(int idxOfTen) {
		List<GameRec> games = new LinkedList<GameRec>();	
		try{
			init();
			ResultSet rs = gamesPstmt.executeQuery();
			
			boolean moved = rs.relative(idxOfTen * 10);
			int numFetched = 0;
			if(moved){
				while(rs.next() && numFetched < 10){
					String creator = rs.getString("creator");
					String winner = rs.getString("winner");
					int points = rs.getInt("points_won");
					long startTime = rs.getTimestamp("start_time").getTime();
					String gl = new String(rs.getBytes("gamelog"));
					
					GameRecordMsg grm = gson.fromJson(gl, GameRecordMsg.class);
					GameRec gr = new GameRec(creator, winner, points, startTime, grm);
					games.add(gr);
					numFetched++;
				}
			}
			
			rs.close();
		}catch(Exception e){
			ready = false;
		}
		return games.toArray(new GameRec[0]);
	}
}
